/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package pos.system.database;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.swing.JOptionPane;

/**
 *
 * @author Shameera
 */
public class DBCreator {

    Connection con = null;
    Statement st = null;
    private boolean isDBCreated = false;
    private boolean isDatabaseExist = false;

    public DBCreator() {
//        init();
    }

    private void init() {
        try {
            Class.forName("com.mysql.jdbc.Driver");
            con = DriverManager.getConnection("jdbc:mysql://localhost:3306/leafpos", "root", "");
            st = con.createStatement();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public static void main(String[] args) {
        DBCreator creator = new DBCreator();
        creator.create();
    }

    public void create() {
        if (creatDatabase()) {
            init();
            createTables();
            createGrantedDBUser();
            close();
        }
    }

    private boolean creatDatabase() {
        try {
            Class.forName("com.mysql.jdbc.Driver");
            Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/", "root", "");
            ArrayList<String> list = new ArrayList<String>();
            Statement st = con.createStatement();
            DatabaseMetaData meta = con.getMetaData();
            ResultSet rs = meta.getCatalogs();
            while (rs.next()) {
                String listofDatabases = rs.getString("TABLE_CAT");
                list.add(listofDatabases);
            }
            if (list.contains("leafpos")) {
                System.out.println("Database already exists");
                isDBCreated = false;
                isDatabaseExist = true;
            } else {
                isDatabaseExist = false;
                int ret = JOptionPane.showConfirmDialog(null, "Database leafpos is not exist do you want to creat it?", "leafpos database not exist", JOptionPane.YES_NO_OPTION);
                if (ret == 0) {
                    st.executeUpdate("CREATE DATABASE leafpos");
                    System.out.println("Database is created");
                    isDBCreated = true;
                } else {
                    isDBCreated = false;
                }

            }
            rs.close();
        } catch (Exception e) {
            e.printStackTrace();
        }

        return isDBCreated;
    }

    private void createTables() {
        createStockTable();
        createCredentialTable();
        createInvoiceTable();
        createRoleTable();
        createOrderTable();
    }

    private void createGrantedDBUser() {
        try {
            st.executeUpdate("CREATE USER '"
                    + DBConstant.DEFAULT_USERNAME + "'@'localhost' IDENTIFIED BY '"
                    + DBConstant.DEFAULT_PASSWORD + "';");
            st.executeUpdate("GRANT ALL ON "
                    + DBConstant.LEAFPOS_DATABASE + ".* TO '" + DBConstant.DEFAULT_USERNAME + "'@'localhost' IDENTIFIED BY '" + DBConstant.DEFAULT_PASSWORD + "' WITH GRANT OPTION;");
            //GRANT ALL classicmodels.* TO 'super2'@'%' WITH GRANT OPTION;
            //GRANT ALL classicmodels.* TO 'super2'@'%' WITH GRANT OPTION;
            System.out.println("User created");
        } catch (SQLException ex) {
            System.out.println("Error creating User");
        }
    }

    private void createStockTable() {
        try {
            st.executeUpdate("CREATE TABLE IF NOT EXISTS "
                    + DBConstant.STOCK_TABLE_DB_STRING + "(" + DBConstant.STOCK_CODE + " VARCHAR(255) PRIMARY KEY NOT NULL, "
                    + DBConstant.STOCK_NAME + " VARCHAR(255) NOT NULL,"
                    + DBConstant.STOCK_SELLER + " VARCHAR(255) NOT NULL,"
                    + DBConstant.STOCK_QTY + " INT NOT NULL,"
                    + DBConstant.STOCK_UNIT_PRICE + " VARCHAR(255) NOT NULL,"
                    + DBConstant.STOCK_DATE + " DATE NOT NULL,"
                    + DBConstant.STOCK_TIMESTAMP + " TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP);");
            System.out.println("stock table created");
        } catch (SQLException ex) {
            System.out.println("Error while creating stock table");
        }
    }

    private void createCredentialTable() {
        try {
            st.executeUpdate("CREATE TABLE IF NOT EXISTS "
                    + DBConstant.CREDENTIAL_TABLE_DB_STRING + "(" + DBConstant.CREDENTIAL_USERNAME + " VARCHAR(255) PRIMARY KEY NOT NULL, "
                    + DBConstant.CREDENTIAL_PASSWORD + " VARCHAR(255) NOT NULL,"
                    + DBConstant.CREDENTIAL_ROLE + " INT NOT NULL,"
                    + DBConstant.CREDENTIAL_DATE + " DATE NOT NULL"
                    + ");");
            updateDefaultCredential();
            System.out.println("credential table created");

        } catch (SQLException ex) {
            System.out.println("Error while creating credential table");
        }
    }

    private void createInvoiceTable() {
        try {
            st.executeUpdate("CREATE TABLE IF NOT EXISTS "
                    + DBConstant.INVOICE_TABLE_DB_STRING + "(" + DBConstant.INVOICE_INVOICE + " VARCHAR(255) PRIMARY KEY NOT NULL, "
                    + DBConstant.INVOICE_USER + " VARCHAR(255) NOT NULL,"
                    + DBConstant.INVOICE_ITEM_COUNT + " INT NOT NULL,"
                    + DBConstant.INVOICE_TOTAL + " VARCHAR(255) NOT NULL,"
                    + DBConstant.INVOICE_DATE + " DATE NOT NULL,"
                    + DBConstant.INVOICE_TIMESTAMP + " TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP);");
            System.out.println("invice table created");

        } catch (SQLException ex) {
            System.out.println("Error while creating invoice table");
        }
    }

    private void createRoleTable() {
        try {
            st.executeUpdate("CREATE TABLE IF NOT EXISTS "
                    + DBConstant.ROLE_TABLE_DB_STRING + "(" + DBConstant.ROLE_ID + " INT PRIMARY KEY NOT NULL, "
                    + DBConstant.ROLE_NAME + " VARCHAR(255) NOT NULL,"
                    + DBConstant.ROLE_PERMISSION + " VARCHAR(255));");
            System.out.println("role table created");
            updateRoleTable();
        } catch (SQLException ex) {
            System.out.println("Error while creating role table");
        }
    }

    private void createOrderTable() {
        try {
            st.executeUpdate("CREATE TABLE IF NOT EXISTS "
                    + DBConstant.ORDER_TABLE_DB_STRING + "(" + DBConstant.ORDER_INVOICE + " VARCHAR(255) NOT NULL, "
                    + DBConstant.ORDER_ITEM_ID + " INT  NOT NULL,"
                    + DBConstant.ORDER_CODE + " VARCHAR(255) NOT NULL,"
                    + DBConstant.ORDER_ITEM_NAME + " VARCHAR(255) NOT NULL,"
                    + DBConstant.ORDER_QTY + " INT NOT NULL,"
                    + DBConstant.ORDER_SELLER + " VARCHAR(255) NOT NULL,"
                    + DBConstant.ORDER_UNIT_PRICE + " VARCHAR(255) NOT NULL , "
                    + "PRIMARY KEY (" + DBConstant.ORDER_INVOICE + "," + DBConstant.ORDER_ITEM_ID + "));");
            System.out.println("order table created");

        } catch (SQLException ex) {
            System.out.println("Error while creating order table");
        }
    }

    private void close() {
        if (con != null) {
            try {
                st.close();
                con.close();
            } catch (SQLException ex) {
                System.out.println("Error while closing connections");
            }
        }
    }

    public boolean isIsDBCreated() {
        return isDBCreated;
    }

    public boolean isIsDatabaseExist() {
        return isDatabaseExist;
    }

    private void updateRoleTable() {
        try {
            st.execute("INSERT INTO `leafpos`.`role` (`ID` ,`NAME` ,`PERMISSION`)VALUES ('1', 'Administrator', NULL), ('2', 'Manager', NULL),('3', 'Employee', NULL),('4', 'User', NULL);");
        } catch (SQLException ex) {
            Logger.getLogger(DBCreator.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    private void updateDefaultCredential() {
        try {
            st.execute("INSERT INTO `leafpos`.`credential` (`USERNAME` ,`PASSWORD` ,`ROLE` , `DATE`)VALUES ('sham', 'sham', '2',CURRENT_DATE()),  ('admin', 'admin', '1',CURRENT_DATE());");
        } catch (SQLException ex) {
            Logger.getLogger(DBCreator.class.getName()).log(Level.SEVERE, null, ex);
        }
    }
}
